package com.sjq.common.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import com.sjq.common.dao.GenericDao;
import com.sjq.common.dao.UserDao;
import com.sjq.common.entity.User;
import com.sjq.common.util.PageResults;

@Repository("userDao")
public class UserDaoImpl extends GenericDao<User> implements UserDao {
	
    @Override
    public User getUserByAccount(String userAccount) {
        String sql = "select * from T_SYS_JY_JYXX where jh = ? ";
        final User user = new User();
        this.getJdbcTemplate().query(sql, new Object[]{userAccount}, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                user.setYHM(rs.getString("yhm"));
                user.setJH(rs.getString("jh"));
                user.setYHMM(rs.getString("yhmm"));
                user.setXM(rs.getString("xm"));
                user.setDWBM(rs.getString("dwbm"));
                user.setROLEDM(rs.getString("roledm"));
                user.setDWMC(rs.getString("dwmc"));
                user.setLXFS(rs.getString("lxfs"));
            }
        });
        return user;
    }

    //新增用户
	@Override
	public void insertUser(User user) {
		StringBuilder sql = new StringBuilder()
				.append(" insert into t_sys_jy_jyxx ")
				.append(" (yhm,gmsfhm,xm,jh,dwbm,dwmc,lxfs,yhmm,roledm,djsj,djbgsj,djbgbs) ")
				.append(" values(?,?,?,?,?,?,?,'8','0099',sysdate,sysdate,'1') ");
        Object[] params = new Object[]{
        		user.getYHM(),
        		user.getGMSFHM(),
        		user.getXM(),
        		user.getJH(),
        		user.getDWBM(),
        		user.getDWMC(),
        		user.getLXFS()
        };
        //System.out.println(sql+"AddUser");
        //System.out.println(params);
        this.getJdbcTemplate().update(sql.toString(),params);
	}

	//删除
	@Override
	public void deleteUserById(String userId) {
		//String sql = " delete from t_wf_sys_user where user_id=? ";
		String sql = "delete from t_sys_jy_jyxx where yhm=?";
		this.getJdbcTemplate().update(sql, userId);
	}
	
    //修改用户
	@Override
	public void updateUser(User user) {
		StringBuilder sql = new StringBuilder()
				.append(" update t_sys_jy_jyxx set ")
				.append(" xm=?, ")
				.append(" gmsfhm=?, ")
				.append(" jh=?, ")
				.append(" dwbm=?, ")
				.append(" dwmc=?, ")
				.append(" lxfs=?, ")
				.append(" djbgsj=sysdate,djbgbs='2'")
				.append(" where yhm=? ");
		Object[] params = new Object[] {
				user.getXM(),
				user.getGMSFHM(),
				user.getJH(),
				user.getDWBM(),
				user.getDWMC(),
				user.getLXFS(),
				user.getYHM()
		};
		this.getJdbcTemplate().update(sql.toString(), params);
	}
	
	//查询单个用户
	@Override
	public Map<String, Object> selectUserById(String userId) {
		StringBuilder sql = new StringBuilder()
				//.append(" select * from t_sys_jy_jyxx where yhm=?
				.append(" select yhm,gmsfhm,xm,jh,dwbm,dwmc,yhmm,(select jsmc from t_pz_sys_role a where a.js_id = t.roledm) rolemc,roledm,lxfs from t_sys_jy_jyxx t where yhm = ? ");
		Object[] params = new Object[] { userId };
		Map<String, Object> map = this.getJdbcTemplate().queryForMap(sql.toString(),params);
		return map;
	}
	
	//分页查询
	@Override
	public PageResults<?> selectUserByPage(int pageNo, int pageSize, User user) {
		StringBuilder paramSql = new StringBuilder();
		List params = new ArrayList();
		if (user.getDWMC() != null&& !(user.getDWMC().equals(""))) {
			paramSql.append(" and dwmc like ? ");
			params.add("%" + user.getDWMC().trim() + "%");
		}
		if (user.getJH() != null&& !(user.getJH().equals(""))) {
			paramSql.append(" and jh = ? ");
			params.add(user.getJH().trim());
		}
		PageResults<Map<String, Object>> results = new PageResults<Map<String, Object>>();
		StringBuilder pageSql = new StringBuilder()
				.append("select * from ")
				.append(" (select tmp_1.*, rownum as rowindex from (select yhm,gmsfhm,xm,jh,dwbm,dwmc,yhmm,(select jsmc from t_pz_sys_role a where a.js_id = t.roledm) rolemc,roledm from t_sys_jy_jyxx t where 1=1 "+ paramSql.toString()+") tmp_1 ) tmp_2 ")
				.append(" where rowindex between ")
				.append(((pageNo-1)*pageSize + 1))
				.append(" and ")
				.append(((pageNo - 1) * pageSize + pageSize));
				//.append(" order by dwbm ");
		System.out.println("pageSql:"+pageSql);
		List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(pageSql.toString(),params.toArray());
		results.setPageSize(pageSize);
		String sql="select count(1) num from t_sys_jy_jyxx where 1=1 "+paramSql.toString()+" ";
		SqlRowSet sqlRowSet = this.getJdbcTemplate().queryForRowSet(sql,params.toArray());
		int count = 0;
		while(sqlRowSet.next()){
			count = sqlRowSet.getInt("num");
		}
		results.setTotalCount(count);
        results.setPageNo(pageNo);
		results.setResults(list);
		return results;
	}

	//根据用户名修改用户密码
	@Override
	public void updateByUserpsd(String xyhmm ,String userId) {
		StringBuilder sql = new StringBuilder()
			.append(" update t_sys_jy_jyxx set ")
			.append(" yhmm = ?")
			.append(" where yhm=? ");
		Object[] params = new Object[] { xyhmm,userId };
		this.getJdbcTemplate().update(sql.toString(), params);
	}

	@Override
	public void updateByUserRole(String yhm, String roleid) {
		StringBuilder sql = new StringBuilder()
			.append(" update t_sys_jy_jyxx set ")
			.append(" roledm = ?")
			.append(" where yhm = ?");
		Object[] params = new Object[]{roleid,yhm};
		this.getJdbcTemplate().update(sql.toString(), params);
	}

	@Override
	public User getUserByGmsfhm(String gmsfhm) {
		  String sql = "select * from T_SYS_JY_JYXX where gmsfhm = ? ";
	        final User user = new User();
	        this.getJdbcTemplate().query(sql, new Object[]{gmsfhm}, new RowCallbackHandler() {

	            @Override
	            public void processRow(ResultSet rs) throws SQLException {
	                user.setYHM(rs.getString("yhm"));
	                user.setJH(rs.getString("jh"));
	                user.setYHMM(rs.getString("yhmm"));
	                user.setXM(rs.getString("xm"));
	                user.setDWBM(rs.getString("dwbm"));
	                user.setROLEDM(rs.getString("roledm"));
	                user.setDWMC(rs.getString("dwmc"));
	                user.setLXFS(rs.getString("lxfs"));
	            }
	        });
	        return user;
	}

	

}
